OLAP Data Cube Wizard - Dimensions
Dimensions are the database fields that you want to use to group the measures (data).
- Pick the fields from the Fact Table query that you wish to use as dimensions.
- Configure column and row dimensions that will be active when you first run the cube.
- Configure dimension options such as display name, sorting methods, and forecasting.
Fields available to be used as dimensions
These are all of the fields from the SELECT clause of the Fact Table. Double click on a field to add it as an Available Dimension or drag a field into the Column or Row tab to make it a default column or row dimension at run-time.
Note: If you elected to build your cube using multiple datasets, you will not see your Dimension query fields listed. To add these fields, select the Fact Table foreign key that links to the dimension table, then click the radio button option to pull the data "from another data set." Argos will ask you which field to use from the dataset you indicate. For example, if your Fact Table had a foreign key called "GenderKey" that linked it to the Gender dimension table, you would select GenderKey for your field, then browse the Gender table for the actual field you wish to display. This is often more complicated than simply building a single query that links your Fact and Dimension tables together.
Available tab
Dimensions that you add to the Available tab are available for the Report Viewer to use, but are not initially included in the cube. Report Viewers can drag dimensions from the Available list into the OLAP cube as either row or column dimensions. This gives the user the ability to configure their view of the data as needed.
Column tab
Dimensions placed in the Column tab will be the default column dimensions in the cube.
Row tab
Dimensions placed in the Row tab will be the default row dimensions in the cube.
Right-clicking anywhere within the Available, Column, or Row tabs allows you to sort the listed dimensions alphabetically.
Display Name
The title to be displayed for each dimension. The default is the name of the field.
Data for this dimension comes…
- directly from the Fact table - The field for this dimension comes from one of the SELECTed fields in the Fact Table.
- from another data set - The field for this dimension comes from one of the datasets in addition to the Fact Table. See the note above (under Fields available to be used as dimensions) for more information.
Default Sort Method
- No sort - Sort is controlled by the ORDER BY clause of the Fact Table.
- Sort by keyfield value - Sort is controlled by the alphanumeric value of the dimension.
- Sort by name - Sort is controlled by the name
Forecasting
Enabling forecasting for a dimension uses an algorithm to calculate one previous ("preceding") and one future ("consequent") value for that row or column based on trends in the data. Argos provides six different forecasting algorithms for you to choose from:
- No forecasting - Do not include any forecasted values in the data.
- Moving average - The best-known forecasting method is the moving averages method. It simply takes a certain number of past periods and adds them together, then divides the result by the number of periods. Simple moving averages (MA) is an effective and efficient method provided the time series is stationary in both mean and variance. The following formula is used in finding the moving average of order n, MA(n) for a period t+1:
MAt+1 = [Dt + Dt-1 + ... +Dt-n+1] / n
where n is the number of observations used in the calculation. - Weighted moving average - Very powerful and economical. It is widely used where repeated forecasts require methods like sum-of-the-digits and trend adjustment methods. An example of a weighted moving averages calculation is as follows:
Weighted MA(3) = w1.Dt + w2.Dt-1 + w3.Dt-2
where the weights are any positive numbers such that: w1 + w2 + w3 =1. Typical weights for this example are: w1 = 3/(1 + 2 + 3) = 3/6, w2 = 2/6, and w3 = 1/6. - Double/Triple exponential smoothing - One of the most successful forecasting methods is the exponential smoothing (ES) method. While the simple MA method is a special case of the ES, the ES is more conservative in its data usage. It also offers the following advantages:
It can be modified to be used effectively for time series with seasonal patterns.
It is easy to adjust for past errors.
It is easy to prepare follow-on forecasts, which are ideal for situations where many forecasts must be prepared and several different forms are used depending on the presence of trend or cyclical variations.
In short, an ES is an averaging technique that uses unequal weights, where the weights applied to past observations decline in an exponential manner, as follows:
Ft+1 = a Dt + (1 - a) Ft
where:
Dt is the actual value
Ft is the forecasted value
a is the weighing factor, which ranges from 0 to 1
t is the current time period.
Notice that the smoothed value becomes the forecast for period t + 1.
A small value for 'a' provides a lot of smoothing while a large value provides a fast response to the recent changes in the time series and a smaller amount of smoothing. Notice that the exponential smoothing and simple moving average techniques will generate forecasts having the same average age of information if moving average of order n is the integer part of (2-a)/a.
An exponential smoothing over an already-smoothed time series is called double-exponential smoothing. In some cases, it might be necessary to extend it to triple-exponential smoothing. While simple exponential smoothing requires stationary condition, the double-exponential smoothing can capture linear trends and triple-exponential smoothing can handle almost all other business time series.
- Show only min/max values - The "Preceding" and "Consequent" fields will contain the min and max values that appear in that row or column.
- Show first and last values - The "Preceding" and "Consequent" fields will repeat the first and last values that appear in that row or column.
Note: If a dimension has fewer than three data points, the forecasting value will be 0 as there is not enough information to extrapolate a value. Forecasted values are not part of the cube data, so some views of the measure (like ranks, record count, prev/next items, etc.) will be 0 when applied to rows or columns with forecasted values. Forecasted values are not included in total/subtotal calculations, but are included in running total calculations.
Up/Down Arrows
Controls the order of display for dimensions. Select a measure and click the Up arrow to move it closer to the front or click the Down arrow to move it closer to the back.
Delete button
Select a dimension and click the red X button to delete the dimension.
Need More Help?
If you need additional help creating OLAP Cubes, refer to the Getting Started with OLAP Cubes page.